# coding=utf-8
import aiomysql

class Material2Problem:

    async def material_problem(self, material_id_list: list):
        '''
        :param material_id_list: 一维的材料ID列表 list[int]
        :return: problem_id_list: 一维的问题ID列表 list[int]
        '''

        conn = None
        cursor = None
        try:

            conn = await aiomysql.connect(
                host='192.168.2.244',
                port=3306,
                user='root',
                password='linkAge@12345',
                charset='utf8mb4',
                db='stock_exchange'
            )

            cursor = await conn.cursor()
            problem_id_list = []
            for id in material_id_list:
                await cursor.execute("SELECT material_id, problem_id FROM material WHERE material_id = %s", (id,))
                material_results = await cursor.fetchone()
                problem_id_list.append(material_results[1])
            return problem_id_list

        except Exception as e:
            # print(f"C3 error: {e}")
            return f"C3 error : {e}"
        finally:
            if cursor:
                await cursor.close()  # 使用 await 关键字
            if conn:
                conn.close()

    async def material_problem_db(self, pool: aiomysql.Pool,material_id_list: list):
        '''
        :param material_id_list: 一维的材料ID列表 list[int]
        :return: problem_id_list: 一维的问题ID列表 list[int]
        '''

        try:
            if not pool:
                raise RuntimeError("数据库连接池未初始化")
            async with pool.acquire() as conn:
                async with conn.cursor() as cursor:
                    problem_id_list = []
                    for id in material_id_list:
                        await cursor.execute("SELECT material_id, problem_id FROM material WHERE material_id = %s", (id,))
                        material_results = await cursor.fetchone()
                        problem_id_list.append(material_results[1])
                    return problem_id_list

        except Exception as e:
            # print(f"C3 error: {e}")
            return f"C3 error : {e}"
        # finally:
        #     if pool:
        #         pool.close()
        #         await pool.wait_closed()
        #         print("数据库连接池已关闭")



if __name__ == '__main__':
    import asyncio

    Mater = Material2Problem()
    # material_id_list = [[1, 2, 3, 4, 5, 6], [11, 21, 31, 41, 51, 61]]
    content_text = '''2014 年 10 月 29 日，利元亨精密总经理、法定代表人 周俊豪因涉嫌犯虚开增值税专用发票罪被大冶市公安局开事拘留，利元亨精密无 法正常经营。2014年11月18日，利元亨精密的其他股东周俊雄、卢家红和周 俊杰出资设立了发行人，收购了利元享精密与生产相关的主要资产，承接了主要 业务和人员。
2、资产收购
公司向利元享精密收购与生产相关的原材料、在产品及机器、运输等设备共 7,961.51 万元，交易价格参考评估价格或经协商确定。'''
    material_id_list = [1720, 1805, 297]
    # material_id_list = [[1720, 1805, 297], [147, 70, 2555]]
    problem_id_list = asyncio.run(Mater.material_problem(material_id_list))
    print(problem_id_list)  # [{'问题ID列表': [569, 538, 86], '问题题目': '关于关联交易'}, {'问题ID列表': [50, 22, 791], '问题题目': '关于采购与供应商'}

